package org.zsqx.dao;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.zsqx.bean.Colors;
import org.zsqx.bean.Disaster;
import org.zsqx.bean.DisasterReport;
import org.zsqx.bean.SumReports;

import cDao.CdaoFactory;
import cDao.DatabaseConnection;
import cDao.HelpDao;
import cDao.RealDao;

public class ReportsDAOImpl implements ReportsDAO{
	private static List<SumReports> reportList;
	private static SumReports conditionReport;
	private static String disasterType="town";
	
	public static List getReport(Object bean, String sql) throws SQLException,
	InstantiationException, IllegalAccessException, SecurityException,
	NoSuchFieldException {
		Connection conn = DatabaseConnection.getConn();
		return getReport(bean, sql, conn);
	}
	public static List getQxjDatabaseReport(Object bean, String sql) throws SQLException,
	InstantiationException, IllegalAccessException, SecurityException,
	NoSuchFieldException {
		Connection conn = DatabaseConnection.getSecondeConn();
		return getReport(bean, sql, conn);
	}
	public static List getReport(Object bean, String sql,Connection conn) throws SQLException,
			InstantiationException, IllegalAccessException, SecurityException,
			NoSuchFieldException {
		Class c = bean.getClass();
		Field[] fields = c.getDeclaredFields();
		List rsList = new ArrayList();
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(sql);
		ResultSetMetaData rsmd = rs.getMetaData();
		while (rs.next()) {
			Object newBean = c.newInstance();
			for (int i = 1; i <= rsmd.getColumnCount(); i++) {
				String columnName = rsmd.getColumnName(i);
				String columnType = rsmd.getColumnTypeName(i);
				String fildName = HelpDao.sqlFieldToProperty(columnName.toLowerCase());
				String methodName = HelpDao.getSetMethod(fildName);
				Class fieldType = getFieldTypeFromColumnType(columnType);
				Object value = null;
				if (columnType.equals("numeric")) {
					value = rs.getDouble(columnName);
				} else if (columnType.equals("decimal")){
					value = rs.getDouble(columnName);
				} else if (columnType.equals("smalldatetime")){
					value = rs.getTimestamp(columnName);
				} else if (columnType.equals("smallint")){
					value = rs.getInt(columnName);
				} else if (columnType.equals("datetime")){
					value = rs.getTimestamp(columnName);
				} else {
					value = rs.getObject(columnName);
				}
				if(fieldType!=null){
					HelpDao.methodInvoke(c, newBean, methodName, fieldType, value);
				}
			}
			rsList.add(newBean);
		}
		rs.close();
		conn.close();
		System.out.println("报表ＳＱＬ：" + sql);
		reportList = rsList;
		return rsList;
	}
	public static Class getFieldTypeFromColumnType(String columnType){
		Class fieldType = null;
		if(columnType.equals("numeric"))fieldType = Double.class;
		if(columnType.equals("decimal"))fieldType = Double.class;
		if(columnType.equals("int"))fieldType = Integer.class;
		if(columnType.equals("smallint"))fieldType = Integer.class;
		if(columnType.equals("nvarchar"))fieldType = String.class;
		if(columnType.equals("varchar"))fieldType = String.class;
		if(columnType.equals("char"))fieldType = String.class;
		if(columnType.equals("smalldatetime"))fieldType = Date.class;
		if(columnType.equals("datetime"))fieldType = Date.class;
		return fieldType;
	}
	private String whereSql(SumReports report){
		String sql = "";
		boolean bw = true;
		if (report.getTown() != null) {
			if (bw) {
				sql += " where";
				bw = false;
			} else {
				sql += " and";
			}
			sql += " town = '" + report.getTown() + "'";
		}
		if (report.getDstype() != null) {
			if (bw) {
				sql += " where";
				bw = false;
			} else {
				sql += " and";
			}
			sql += " distypename = '" + report.getDstype() + "'";
		}
		if (report.getYear() != null) {
			if (bw) {
				sql += " where";
				bw = false;
			} else {
				sql += " and";
			}
			sql += " DatePart(year,starttime)=" + report.getYear();
		}
		if (report.getMonth() != null) {
			if (bw) {
				sql += " where";
				bw = false;
			} else {
				sql += " and";
			}
			sql += " DatePart(month,starttime)=" + report.getMonth();
		}
		if (report.getTimeStage() != null) {
			if (bw) {
				sql += " where";
				bw = false;
			} else {
				sql += " and";
			}
			sql += " DatePart(year,starttime)+" + report.getTimeStage()
					+ " >= DatePart(year,getDate())";
		}
		if(report.getStartYear()!=null){
			if (bw) {
				sql += " where";
				bw = false;
			} else {
				sql += " and";
			}
			sql += " DatePart(year,starttime) between " + report.getStartYear()+" and "
					+ report.getEndYear();
		}
		return sql;
	}

	public List getTownReport(SumReports report)
			throws SecurityException, SQLException, InstantiationException,
			IllegalAccessException, NoSuchFieldException {
		String sql = "select SUM(economyloss) AS money_Loss, SUM(deadqt) AS dead, SUM(hitsbydisqt) AS dst_All, SUM(missqt) AS lost, SUM(injuredqt) AS hurt,"
				+ " town AS town,COUNT(*) AS act_Time from disaster";
		sql += whereSql(report);
		sql += " group by town having town is not null";
		conditionReport = report;
		disasterType="town";
		return getReport(new SumReports(), sql);
	}

	public List getYearReport(SumReports report)
			throws SecurityException, SQLException, InstantiationException,
			IllegalAccessException, NoSuchFieldException {
		String sql = "select SUM(economyloss) AS money_Loss, SUM(deadqt) AS dead, SUM(hitsbydisqt) AS dst_All, SUM(missqt) AS lost, SUM(injuredqt) AS hurt,"
				+ " town AS town,COUNT(*) AS act_Time from disaster";
		sql += whereSql(report);
		sql += " group by town having town is not null";
		conditionReport = report;
		disasterType="year";
		return getReport(new SumReports(), sql);
	}

	public List getMonthReport(SumReports report)
			throws SecurityException, SQLException, InstantiationException,
			IllegalAccessException, NoSuchFieldException {
		String sql = "select SUM(economyloss) AS money_Loss, SUM(deadqt) AS dead, SUM(hitsbydisqt) AS dst_All, SUM(missqt) AS lost, SUM(injuredqt) AS hurt,"
				+ " distypename AS dstype,COUNT(*) AS act_Time from disaster";
		sql += whereSql(report);
		sql += " group by distypename";
		conditionReport = report;
		disasterType="month";
		return getReport(new SumReports(), sql);
	}

	public List getGjReport(SumReports report)
			throws SecurityException, SQLException, InstantiationException,
			IllegalAccessException, NoSuchFieldException {
		String sql = "select SUM(tgnumber) AS tgnumber, SUM(sblose) AS sblose, SUM(planedelaynumber) AS planedelaynumber, SUM(fqvehicledestroy) AS fqvehicledestroy,"
				+ " SUM(ghmorratime) AS ghmorratime, SUM(sinkboatnumbrt) AS sinkboatnumbrt, SUM(jeconomyloss) AS jeconomyloss,"
				+ " town AS town,COUNT(*) AS act_Time from disaster";
		disasterType="gj";
		return getDisReportGroupByTown(report, sql);
	}

	public List getNymReport(SumReports report)
			throws SecurityException, SQLException, InstantiationException,
			IllegalAccessException, NoSuchFieldException {
		String sql = "select SUM(szarea) AS szarea, SUM(czarea) AS czarea, SUM(ssarea) AS ssarea, SUM(foodstuflose) AS foodstuflose,"
				+ " SUM(neconomyloss) AS neconomyloss, SUM(sllosenumber) AS sllosenumber, SUM(leconomyloss) AS leconomyloss,"
				+ " SUM(sslnumber) AS sslnumber, SUM(szmcarea) AS szmcarea, SUM(meconomyloss) AS meconomyloss,"
				+ " SUM(sinkboatnumber) AS sinkboatnumber, SUM(sinkboatweight) AS sinkboatweight, SUM(yeconomyloss) AS yeconomyloss,"
				+ " town AS town,COUNT(*) AS act_Time from disaster";
		disasterType="nym";
		return getDisReportGroupByTown(report, sql);
	}

	private List getDisReportGroupByTown(SumReports report, String sql)
			throws SecurityException, SQLException, InstantiationException,
			IllegalAccessException, NoSuchFieldException {
		sql += whereSql(report);
		sql += " group by town having town is not null";
		conditionReport = report;
		return getReport(new DisasterReport(), sql);
	}

	public List<SumReports> getResults() {
		return reportList;
	}
	
	
	public List<Disaster> getTownReportSingle(String townName){
		System.out.println(townName);
		String sql = "select * from disaster";
		conditionReport.setTown(townName);
		sql += whereSql(conditionReport);
		System.out.println(sql);
		List<Disaster> reportSingleList = null;
		try {
			reportSingleList = getReport(new Disaster(), sql);
		} catch (SecurityException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (NoSuchFieldException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return reportSingleList;
	}
	public static void main(String[] args) throws SecurityException, SQLException, InstantiationException, IllegalAccessException, NoSuchFieldException {
		ReportsDAOImpl rd =new ReportsDAOImpl();
//		List list = rd.getTownReport(new SumReports());
//		List i =rd.getTownReportSingle("小榄");
//		System.out.println(i);
		rd.getColors();
	}
	
	public String getColors() {
		RealDao rd = CdaoFactory.getRealDao();
		String colors=((Colors)rd.find(new Colors()).get(0)).getColors();
		System.out.println(colors);
		return colors;
	}
	public String resetColors(String colors) {
		RealDao rd = CdaoFactory.getRealDao();
		Colors c=new Colors();
		c.setId(1);
		c.setColors(colors);
		if (rd.update(c)>0) {
			return "suc";
		}
		return "fail";
	}
	public String getDisasterType() {
		return this.disasterType;
	}
}
